package com.tisson.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.pojo.Admin;
import com.tisson.pojo.Manager;

public class AdminDAO {
	private String sqlStr;

	public List combineQuery(String name, String departId, String areaId,
			String role) throws SQLException {
		sqlStr = "select * from admin,admin_area,admin_depart,ac_department,ac_area ";
		String where = "where admin.id = admin_depart.admin_id and depart_id = DepartmentId "
				+ "and admin.id = admin_area.admin_id and area_id = AreaId";
		String a, b, c, d;
		if (departId == null || departId.equals(""))
			a = " and DepartmentId like '%'";
		else
			a = " and DepartmentId = '" + departId + "'";
		if (name == null || name.equals(""))
			b = " and name like '%'";
		else
			b = " and name like '%" + name + "%'";
		if (areaId == null || areaId.equals(""))
			c = " and AreaId like '%'";
		else
			c = " and AreaId = '" + areaId + "'";
		if (role == null || role.equals(""))
			d = " and role like '%'";
		else
			d = " and role = '" + role + "'";
		sqlStr = sqlStr + where + a + b + c + d;
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		List result = new ArrayList();
		while (rs.next()) {
			Manager manager = new Manager();
			manager.setId(FormatUtil.ConvertUuid(rs.getBytes("id")));
			manager.setName((String) rs.getObject("name"));
			manager.setUsername((String) rs.getObject("username"));
			manager.setPassword((String) rs.getObject("password"));
			manager.setTel((String) rs.getObject("tel"));
			manager.setRole((String) rs.getObject("role"));
			manager.setAdminArea((String) rs.getObject("AreaName"));
			manager.setAdminDepart((String) rs.getObject("DepName"));
			result.add(manager);
		}
		rs.close();
		stmt.close();
		return result;
	}

	public void save(Admin admin) throws SQLException {
		try {
			if (admin != null) {
				sqlStr = "";
				sqlStr += "INSERT INTO admin(name,username,password,tel,role) VALUES(";
				sqlStr += "'" + admin.getName() + "',";
				sqlStr += "'" + admin.getUsername() + "',";
				sqlStr += "'" + admin.getPassword() + "',";
				sqlStr += "'" + admin.getTel() + "',";
				sqlStr += "'" + admin.getRole() + "')";
				Statement stmt = StatementUtil.getStatement();
				stmt.executeUpdate(sqlStr);
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Admin findById(String id) throws SQLException {
		ResultSet rs;
		sqlStr = "";
		sqlStr += "SELECT * FROM admin ";
		sqlStr += "WHERE id = '" + id + "'";
		Statement stmt = StatementUtil.getStatement();
		rs = stmt.executeQuery(sqlStr);
		Admin admin = new Admin();
		while (rs.next()) {
			admin.setId(FormatUtil.ConvertUuid(rs.getBytes("id")));
			admin.setName((String) rs.getObject("name"));
			admin.setUsername((String) rs.getObject("username"));
			admin.setPassword((String) rs.getObject("password"));
			admin.setTel((String) rs.getObject("tel"));
			admin.setRole((String) rs.getObject("role"));
		}
		rs.close();
		stmt.close();
		return admin;
	}

	public Admin findByUsername(String username) {
		ResultSet rs;
		List result = new ArrayList();
		sqlStr = "select * from admin where username = '" + username + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				Admin admin = new Admin();
				admin.setId(FormatUtil.ConvertUuid(rs.getBytes("id")));
				admin.setName((String) rs.getObject("name"));
				admin.setUsername((String) rs.getObject("username"));
				admin.setPassword((String) rs.getObject("password"));
				admin.setTel((String) rs.getObject("tel"));
				admin.setRole((String) rs.getObject("role"));
				result.add(admin);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (Admin) result.get(0);
	}

	public void update(Admin Admin) {
		sqlStr = "";
		sqlStr += "UPDATE admin SET";
		sqlStr += " name = '" + Admin.getName() + "',";
		sqlStr += " username = '" + Admin.getUsername() + "',";
		sqlStr += " password = '" + Admin.getPassword() + "',";
		sqlStr += " tel = '" + Admin.getTel() + "',";
		sqlStr += " role = '" + Admin.getRole() + "'";
		sqlStr += " WHERE id = ";
		sqlStr += "'" + Admin.getId() + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			stmt.executeUpdate(sqlStr);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete(String id) {
		sqlStr = "";
		sqlStr += "DELETE FROM admin ";
		sqlStr += "WHERE id = '" + id + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			stmt.executeUpdate(sqlStr);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List findAll() {
		ResultSet rs;
		List result = new ArrayList();
		sqlStr = "SELECT * FROM admin";
		try {
			Statement stmt = StatementUtil.getStatement();
			rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				Admin admin = new Admin();
				admin.setId(FormatUtil.ConvertUuid(rs.getBytes("id")));
				admin.setName((String) rs.getObject("name"));
				admin.setUsername((String) rs.getObject("username"));
				admin.setPassword((String) rs.getObject("password"));
				admin.setTel((String) rs.getObject("tel"));
				admin.setRole((String) rs.getObject("role"));
				result.add(admin);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
